﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data.SqlClient;
using System.Data;
using System.Data.Common;

using Data_Objects;
using Business_Objects;
namespace Data_Objects.Implementation
{
    //Ko public -> tất cả gọi qua dataacess
    class SqlServerClassDao : IClassDao
    {
        public SqlServerClassDao()
        {
            SQLUtility.TableName = "Class";
        }

        //Trả về đối tượng báo ăn
        private Class MakeClass(DataRow row)
        {
            Guid _id = Guid.Parse(row["ClassID"].ToString());
            Guid _schoolYearID = Guid.Parse(row["SchoolYearID"].ToString());
            Guid _blockID = Guid.Parse(row["BlockID"].ToString());
            string _classNumber = row["ClassNumber"].ToString();
            string _name = row["ClassName"].ToString();
            bool _hoc2buoi = bool.Parse(row["Hoc2buoi"].ToString());
            bool _thuchienCTGDMNmoi = bool.Parse(row["ThuchienCTGDMNmoi"].ToString());
            bool _nhatre = bool.Parse(row["Nhatre"].ToString());
            bool _riengMaugiao5tuoi = bool.Parse(row["RiengMaugiao5tuoi"].ToString());
            bool _mGghep2dotuoi = bool.Parse(row["MGghep2dotuoi"].ToString());
            bool _maugiao = bool.Parse(row["Maugiao"].ToString());
            bool _banTru = bool.Parse(row["BanTru"].ToString());
            bool _nhomDuoi24thang = bool.Parse(row["NhomDuoi24thang"].ToString());
            bool _nhomTu24den36thang = bool.Parse(row["NhomTu24den36thang"].ToString());
            bool _lop3den4tuoi = bool.Parse(row["Lop3den4tuoi"].ToString());
            bool _lop4den5tuoi = bool.Parse(row["Lop4den5tuoi"].ToString());
            bool _lop5den6tuoi = bool.Parse(row["Lop5den6tuoi"].ToString());
            int _schoolID = int.Parse(row["SchoolID"].ToString());
            return new Class(_id, _schoolYearID, _blockID, _classNumber, _name, _hoc2buoi, _thuchienCTGDMNmoi, _nhatre, _riengMaugiao5tuoi, _mGghep2dotuoi, _maugiao, _banTru, _nhomDuoi24thang, _nhomTu24den36thang, _lop3den4tuoi, _lop4den5tuoi, _lop5den6tuoi, _schoolID);
        }

        //Lấy danh sách khách hàng trả về datatable
        private IList<Class> MakeClasss(DataTable dt)
        {
            IList<Class> list = new List<Class>();
            foreach (DataRow row in dt.Rows)
            {
                list.Add(MakeClass(row));
            }
            return list;
        }

        public IList<Business_Objects.Class> GetClass()
        {
            SQLUtility.TableName = "Classe";
            DbParameter[] param = null;
            DataTable dt = CommandProvider.GetDatatable(SQLUtility.ProceduresGetAll, param);
            return MakeClasss(dt); 
        }

        public Business_Objects.Class GetClassByID(string ClassID)
        {
            SQLUtility.TableName = "Class";
            DbParameter[] parameters = new DbParameter[1];
            parameters[0] = new SqlParameter("@ClassID", ClassID);
            DataRow row = CommandProvider.GetDataRow(SQLUtility.ProceduresGetByID, parameters);
            if (row == null) { return null; }
            else
            {
                return MakeClass(row);
            }
        }

        public void InsertClass(Business_Objects.Class Class)
        {
            SQLUtility.TableName = "Class";
            int newID = CommandProvider.Insert(SQLUtility.ProceduresInsert, Take(Class), true);
        }

        public int UpdateClass(Business_Objects.Class Class)
        {
            SQLUtility.TableName = "Class";
            try { return CommandProvider.Update(SQLUtility.ProceduresUpdate, Take(Class)); }
            catch { return -1; }
        }

        public int DeleteClass(Business_Objects.Class Class)
        {
            SQLUtility.TableName = "Class";
            try { return CommandProvider.Delete(SQLUtility.ProceduresDelete, Take(Class)); }
            catch { return -1; }
        }


        private DbParameter[] Take(Class Class)
        {
            DbParameter[] parameters = new DbParameter[18];
            parameters[0] = new SqlParameter("@ClassID", Class.Id);
            parameters[1] = new SqlParameter("@SchoolYearID", Class.SchoolYearID);
            parameters[2] = new SqlParameter("@BlockID", Class.BlockID);
            parameters[3] = new SqlParameter("@ClassNumber", Class.ClassNumber);

            parameters[4] = new SqlParameter("@ClassName", Class.Name);
            parameters[5] = new SqlParameter("@Hoc2buoi", Class.Hoc2buoi);
            parameters[6] = new SqlParameter("@ThuchienCTGDMNmoi", Class.ThuchienCTGDMNmoi);
            parameters[7] = new SqlParameter("@Nhatre", Class.Nhatre);

            parameters[8] = new SqlParameter("@RiengMaugiao5tuoi", Class.RiengMaugiao5tuoi);
            parameters[9] = new SqlParameter("@MGghep2dotuoi", Class.MGghep2dotuoi);
            parameters[10] = new SqlParameter("@Maugiao", Class.Maugiao);
            parameters[11] = new SqlParameter("@BanTru", Class.BanTru);

            parameters[12] = new SqlParameter("@NhomDuoi24thang", Class.NhomDuoi24thang);
            parameters[13] = new SqlParameter("@NhomTu24den36thang", Class.NhomTu24den36thang);
            parameters[14] = new SqlParameter("@Lop3den4tuoi", Class.Lop3den4tuoi);
            parameters[15] = new SqlParameter("@Lop4den5tuoi", Class.Lop4den5tuoi);

            parameters[16] = new SqlParameter("@Lop5den6tuoi", Class.Lop5den6tuoi);
            parameters[17] = new SqlParameter("@SchoolID", Class.SchoolID);

            return parameters;
        }


        public List<Class> GetClassBySchoolIdAndSchoolYearID(int schoolId, string SchoolYearID)
        {
            DbParameter[] parameters = new DbParameter[2];
            parameters[0] = new SqlParameter("@SchoolID", schoolId);
            parameters[1] = new SqlParameter("@SchoolYearID",Guid.Parse( SchoolYearID));
            DataTable dt = CommandProvider.GetDatatable("usp_SelectClassesByAndSchoolYearIDAndSchoolID", parameters);
            return MakeClasss(dt).ToList(); 
        }


        public List<Class> GetClassPhanLopBySchoolIdAndSchoolYearID(string SchoolYearID, int schoolId)
        {
            DbParameter[] parameters = new DbParameter[2];
            parameters[0] = new SqlParameter("@SchoolID", schoolId);
            parameters[1] = new SqlParameter("@SchoolYearID", Guid.Parse(SchoolYearID));
            DataTable dt = CommandProvider.GetDatatable("usp_SelectPhanLopsByAndSchoolYearIDAndSchoolIDAndClassID1", parameters);
            return MakeClasss(dt).ToList(); 
        }


        public List<Class> GetClassBySchoolIdAndSchoolYearIDNotPhanLop(int schoolId, string SchoolYearID)
        {
            DbParameter[] parameters = new DbParameter[2];
            parameters[0] = new SqlParameter("@SchoolID", schoolId);
            parameters[1] = new SqlParameter("@SchoolYearID", Guid.Parse(SchoolYearID));
            DataTable dt = CommandProvider.GetDatatable("usp_SelectPhanLopsByAndSchoolYearIDAndSchoolIDAndNotPhanLop", parameters);
            return MakeClasss(dt).ToList(); 
        }
    }
}
